package com.ysd.dao.Impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.mysql.jdbc.Connection;
import com.ysd.entity.Leansnews;
import com.ysd.util.DBManager;

public class ShangXiaPianDaoImpl {
	//查询上一篇
	public List<Leansnews> getShang(int cId) {
    	List<Leansnews> leansList = new ArrayList<Leansnews>();//定义一个ArrayList
    	Connection conn= (Connection) DBManager.getConnection();//获取连接
    	Statement st=null;//执行
    	ResultSet rs=null;//查询
    	
    	try {//正常情况
			st=conn.createStatement();//创建Statement对象，作用是执行SQL语句
			rs = st.executeQuery("select * from leansnews where newsId = (select newsId from leansnews where newsId < "+cId+" order by newsId desc limit 1)");//执行查询的SQL语句
			while(rs.next()) {//遍历结果集
				int newsId=rs.getInt("newsId");
				String newsTitle=rs.getString("newsTitle");
				String newsContent=rs.getString("newsContent");
				String newsXiangQin=rs.getString("newsXiangQin");
				int newsCatalogueId=rs.getInt("newsCatalogueId");
				Leansnews leansnews = new Leansnews(newsId, newsTitle, newsContent,newsXiangQin,newsCatalogueId); 
				leansList.add(leansnews);//添加至结果集中
			}
			return leansList;
		} catch (SQLException e) {//非正常情况
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
    }
	//查询下一篇
	public List<Leansnews> getXia(int xId) {
    	List<Leansnews> leansList = new ArrayList<Leansnews>();//定义一个ArrayList
    	Connection conn= (Connection) DBManager.getConnection();//获取连接
    	Statement st=null;//执行
    	ResultSet rs=null;//查询
    	
    	try {//正常情况
			st=conn.createStatement();//创建Statement对象，作用是执行SQL语句
			rs = st.executeQuery("select * from leansnews where newsId = (select newsId from leansnews where newsId > "+xId+" order by newsId  limit 1)");//执行查询的SQL语句
			while(rs.next()) {//遍历结果集
				int newsId=rs.getInt("newsId");
				String newsTitle=rs.getString("newsTitle");
				String newsContent=rs.getString("newsContent");
				String newsXiangQin=rs.getString("newsXiangQin");
				int newsCatalogueId=rs.getInt("newsCatalogueId");
				Leansnews leansnews = new Leansnews(newsId, newsTitle, newsContent,newsXiangQin,newsCatalogueId); 
				leansList.add(leansnews);//添加至结果集中
			}
			return leansList;
		} catch (SQLException e) {//非正常情况
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
    }
}
